from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Float, Boolean
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref
from sqlalchemy import Table
from sqlalchemy import create_engine

Base = declarative_base()

queries_terms = Table( 'queries_terms', Base.metadata,
  Column('query_id', Integer, ForeignKey('queries.id')),
  Column('term_id', Integer, ForeignKey('terms.id'))
)

# queries_terms = Table( 'queries_sessions', Base.metadata,
#   Column('query_id', Integer, ForeignKey('queries.id')),
#   Column('session_id', Integer, ForeignKey('sessions.id')),
# )

class QueryProp(Base):
  __tablename__ = 'query_props'

  id = Column(Integer, primary_key=True)
  session_id = Column('session_id', Integer, ForeignKey('sessions.id'))
  query_id = Column('query_id', Integer, ForeignKey('queries.id'))
  serp_id = Column(Integer)
  time_passed = Column(Integer)

class Query(Base):
  __tablename__ = 'queries'

  id = Column(Integer, primary_key=True) # <=> uid
  terms = relationship('Term', secondary = queries_terms, backref = 'terms')

class Term(Base):
  __tablename__ = 'terms'

  id = Column(Integer, primary_key = True) # <=> uid
  #uid = Column(Integer, unique = True)

class Weight(Base):
  __tablename__ = 'weights'

  id = Column(Integer, primary_key=True)
  term_id = Column(Integer, ForeignKey('terms.id'))
  site_id = Column(Integer, ForeignKey('sites.id'))
  user_id = Column(Integer, ForeignKey('users.id'))
  value = Column(Float)

class Site(Base):
  __tablename__ = 'sites'

  id = Column(Integer, primary_key=True) # <=> uid
  domain = Column(Boolean)

class Session(Base):
  __tablename__ = 'sessions'

  id = Column(Integer, primary_key=True) # <=> uid
  day = Column(Integer)
  user_id = Column(Integer, ForeignKey('users.id'))
  user = relationship('User', backref='users')


class User(Base):
  __tablename__ = 'users'

  id = Column(Integer, primary_key=True)
  sessions = relationship('Session', backref='sessions')

class Click(Base):
  __tablename__ = 'clicks'

  id = Column(Integer, primary_key=True)
  site_id = Column(Integer, ForeignKey('sites.id'))
  site = relationship('Site', backref='sites')
  time_passed = Column(Integer)
  serp_id = Column(Integer)

users_rule = """
  DROP RULE IF EXISTS users_on_duplicate_ignore ON users;
  CREATE RULE users_on_duplicate_ignore AS ON INSERT TO users
    WHERE EXISTS(SELECT 1 FROM users
      WHERE id = NEW.id)
    DO INSTEAD NOTHING;
  """

sessions_rule = """
  DROP RULE IF EXISTS sessions_on_duplicate_ignore ON sessions;
  CREATE RULE sessions_on_duplicate_ignore AS ON INSERT TO sessions
    WHERE EXISTS(SELECT 1 FROM sessions
      WHERE id = NEW.id)
    DO INSTEAD NOTHING;
  """

def main(conf):
  engine = create_engine(conf, echo=True)
  Base.metadata.create_all(engine)
  conn = engine.connect()

  conn.execute(users_rule)
  conn.execute(sessions_rule)

if __name__ == "__main__":
  main('postgres://alice:123@localhost:5432/pws_test')